package ch.zbw.pra.sysventory.domain.dbhandling;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;

import ch.zbw.pra.sysventory.domain.dbconnector.IDBConnector;
import ch.zbw.pra.sysventory.domain.util.DateUtil;
import ch.zbw.pra.sysventory.domain.util.MyLogger;
import ch.zbw.pra.sysventory.domain.util.TimeStampGenerator;

public class StatisticData {

	private static final org.slf4j.Logger log = org.slf4j.LoggerFactory
			.getLogger(StatisticData.class);

	private IDBConnector dbconnector;
	private DateUtil dateUtil;

	public StatisticData(IDBConnector dbconnector) {
		this.dbconnector = dbconnector;
		this.dateUtil = new DateUtil();
	}

	public int countOS() {
		int countOS = 0;
		String query = "SELECT COUNT(*) FROM operatingSystem";
		ResultSet rs = this.dbconnector.executeQuery(query);
		try {
			while (rs.next()) {
				countOS = rs.getInt("COUNT(*)");
			}
			rs.close();
			this.dbconnector.closeResultSetAndPreparedStatement();
		} catch (SQLException e) {
			MyLogger.logError(log, e.toString());
			this.dbconnector.rollback();
		}
		MyLogger.logDebug(log, "Anzahl Betriebssysteme: " + countOS);
		return countOS;
	}

	public int countSoftwares() {
		int countSoftwares = 0;
		String query = "SELECT COUNT(*) FROM software";
		ResultSet rs = this.dbconnector.executeQuery(query);
		try {
			while (rs.next()) {
				countSoftwares = rs.getInt("COUNT(*)");
			}
			rs.close();
			this.dbconnector.closeResultSetAndPreparedStatement();
		} catch (SQLException e) {
			MyLogger.logError(log, e.toString());
			this.dbconnector.rollback();
		}
		MyLogger.logDebug(log, "Anzahl Softwarepakete: " + countSoftwares);
		return countSoftwares;
	}

	public int scansThisWeek() {
		Date dateFrom = this.dateUtil.getFirstDateOfActualWeek();
		Date dateTo = this.dateUtil.getLastDateOfActualWeek();
		return scansInTimePeriod(TimeStampGenerator.getTimestampForDate(dateFrom),
				TimeStampGenerator.getTimestampForDate(dateTo));
	}

	public int scansThisMonth() {
		Date dateFrom = this.dateUtil.getFirstDateOfActualMonth();
		Date dateTo = this.dateUtil.getLastDateOfActualMonth();
		return scansInTimePeriod(TimeStampGenerator.getTimestampForDate(dateFrom),
				TimeStampGenerator.getTimestampForDate(dateTo));
	}

	public int scansThisYear() {
		Date dateFrom = this.dateUtil.getFirstDateOfActualYear();
		Date dateTo = this.dateUtil.getLastDateOfActualYear();
		return scansInTimePeriod(TimeStampGenerator.getTimestampForDate(dateFrom),
				TimeStampGenerator.getTimestampForDate(dateTo));
	}

	public int scansInTimePeriod(Timestamp timestampFrom, Timestamp timestampTo) {
		int countScans = 0;
		String query = "SELECT COUNT(*) FROM scanversion WHERE timestampScanversion >= '"
				+ timestampFrom + "' AND timestampScanversion <= '" + timestampTo + "'";
		ResultSet rs = this.dbconnector.executeQuery(query);
		try {
			while (rs.next()) {
				countScans = rs.getInt("COUNT(*)");
			}
			rs.close();
			this.dbconnector.closeResultSetAndPreparedStatement();
		} catch (SQLException e) {
			MyLogger.logError(log, e.toString());
			this.dbconnector.rollback();
		}
		MyLogger.logDebug(log,
				"Anzahl Scans zwischen " + this.dateUtil.getFormattedDate(timestampFrom) + " und "
						+ this.dateUtil.getFormattedDate(timestampTo) + ": " + countScans);
		return countScans;

	}

}
